﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Aspose.Cells;
using System.Data;
using System.IO;

namespace GeoWeb.Demo.Asposes
{
    /// <summary>
    /// Aspose.cells.dll Excel文件导入导出DEMO演示
    /// add by zhangyu
    /// 2013.01.16
    /// </summary>
    public partial class ExcelDemo : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        /// <summary>
        /// 执行EXCEL的导入，获取EXCEL的数据
        /// add by zhangyu
        /// 2013.01.16
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void Btn_GetExcel_Click(object sender, EventArgs e)
        {
            if (!Fu_ExcelUpload.HasFile)
            {
                ClientScript.RegisterStartupScript(this.GetType(), "show", "alert('请选择文件！')", true);
                return;
            }
            //EXCEL文件路径
            string sFilePath = Server.MapPath("Excel") + "\\" + Guid.NewGuid().ToString() + ".xls";

            Fu_ExcelUpload.SaveAs(sFilePath);

            //通过文件路径取得该EXCEL对象
            Workbook GlobalBook = new Workbook(sFilePath);
            //取得导入EXCEL的第一个worksheet页
            Worksheet worksheet = GlobalBook.Worksheets[0];
            //取得当前worksheet页的全部数据
            DataTable dtData = worksheet.Cells.ExportDataTable(0, 0, worksheet.Cells.MaxRow + 1, worksheet.Cells.MaxColumn + 1);

            if (File.Exists(sFilePath))
                File.Delete(sFilePath);

            GvExcel.DataSource = dtData;
            GvExcel.DataBind();
        }

        /// <summary>
        /// Excel导出示例
        /// add by zhangyu
        /// 2013.01.14
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void Btn_ReportExcel_Click(object sender, EventArgs e)
        {
            //获取GeoWeb.DataContext的demo库进行导出
            GeoWeb.Demo.DataContext.DemoDataDal demo = new DataContext.DemoDataDal();
            DataTable dtData = demo.Select2();
            //datatable 的 TableName 在模版中会用到
            dtData.TableName = "MyExcel";
            //Excel模版路径
            string sFilePath = "Excel/Excel导出示例模版.xls";
            sFilePath = Server.MapPath(sFilePath);
            string ExcelName = "Excel导出示例";

            //操作Excel的类
            WorkbookDesigner Designer = new WorkbookDesigner();
            Designer.Open(sFilePath);
            //将datatable的值插入到EXCEL模版中
            Designer.SetDataSource(dtData);

            Designer.SetDataSource("Excel_Depart", "工程二部");
            Designer.SetDataSource("Excel_Author", "zhangyu");
            Designer.SetDataSource("Excel_Date", DateTime.Now.ToString("yyyy年MM月dd日"));

            Designer.Process();
            System.IO.MemoryStream outStream = new MemoryStream();
            Designer.Workbook.Save(outStream, FileFormatType.Excel97To2003);

            outStream.Position = 0;
            Response.Clear();
            Response.Buffer = true;
            Response.Charset = "utf-8";
            Response.AppendHeader("Content-Disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(ExcelName, System.Text.Encoding.UTF8).ToString() + ".xls");
            Response.ContentEncoding = System.Text.Encoding.UTF8;
            Response.ContentType = "application/ms-excel";
            Response.BinaryWrite(outStream.ToArray());
            Response.End();
        }
    }
}